package com.hava.jdbc;

import java.sql.*;
import java.util.*;

/**
 * Created by zhanpeng on 2016/9/21.
 */
public class HelloJDBC {

//    static final String JDBC_DRIVER = "org.postgresql.Driver";
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";

//    static final String DB_URL = "jdbc:postgresql://192.168.1.200/test";
    static final String DB_URL = "jdbc:mysql://192.168.1.200/test";

//    static final String USER = "postgres";
    static final String USER = "root";

//    static final String PASSWORD = "";
    static final String PASSWORD = "dVHJtG0T:pf*";

    public static void helloworld() throws ClassNotFoundException {

        Connection connection = null;

        Statement statement = null;
        //Fetch Statement
        PreparedStatement preparedStatement = null;

        ResultSet resultSet = null;


        // 1. add Driver
        Class.forName(JDBC_DRIVER);

        // 2. create db connnection
        try {
            // add the fetch value for db_url
            String DB_URL_fetch = DB_URL + "?userCursorFetch=true";
            System.out.println("[DB_URL_fetch]:" + DB_URL_fetch);
//            connection = DriverManager.getConnection(DB_URL,USER,PASSWORD);
            connection = DriverManager.getConnection(DB_URL_fetch,USER,PASSWORD);
            System.out.println("getConnection");

            // 3.run SQL

//            statement = connection.createStatement();
            preparedStatement = connection.prepareStatement("SELECT userName FROM user");
            preparedStatement.setFetchSize(1);
//            resultSet = statement.executeQuery("SELECT \"userName\" FROM \"public\".\"user\"");
            resultSet = preparedStatement.executeQuery("SELECT userName FROM user");

            // 4.get userName
            while(resultSet.next())
            {
                Integer index = resultSet.getRow();
                String value = resultSet.getString("userName");
                System.out.println("resultSet [row]:" + index + " [value]:" + value);
            }
        } catch (SQLException e) {
            // Exception
            e.printStackTrace();
        } finally {
            try {

                // 5. close connection
                if(connection != null)
                    connection.close();
                if(statement != null)
                    statement.close();
                if(resultSet != null)
                    resultSet.close();

                } catch (SQLException e) {
                    e.printStackTrace();
                }
        }
    }

    public static void batch(Set<String> users) throws ClassNotFoundException {
        Connection connection = null;

        Statement statement = null;
        //Fetch Statement
//        PreparedStatement preparedStatement = null;

        ResultSet resultSet = null;


        // 1. add Driver
        Class.forName(JDBC_DRIVER);

        // 2. create db connnection
        try {
            // add the fetch value for db_url
//            String DB_URL_fetch = DB_URL + "?userCursorFetch=true";
//            System.out.println("[DB_URL_fetch]:" + DB_URL_fetch);
            connection = DriverManager.getConnection(DB_URL,USER,PASSWORD);
//            connection = DriverManager.getConnection(DB_URL_fetch,USER,PASSWORD);
            System.out.println("getConnection");

            // 3.run SQL

            statement = connection.createStatement();
//            preparedStatement = connection.prepareStatement("SELECT userName FROM user");
//            preparedStatement.setFetchSize(1);
//            resultSet = statement.executeQuery("SELECT \"userName\" FROM \"public\".\"user\"");
//            resultSet = preparedStatement.executeQuery("SELECT userName FROM user");
//
//            // 4.get userName
//            while(resultSet.next())
//            {
//                Integer index = resultSet.getRow();
//                String value = resultSet.getString("userName");
//                System.out.println("resultSet [row]:" + index + " [value]:" + value);
//            }

            Long begin = System.currentTimeMillis();


            for(String user : users) {
                String sql = "INSERT INTO user (userName) VALUE (" + user + ")";
                statement.executeUpdate(sql);

            }

            Long end = System.currentTimeMillis();
            System.out.println("[end - begin]:" + (end - begin));


            begin = System.currentTimeMillis();
            Iterator iterator = users.iterator();

            int batchsize = 10;
            for(int index=0;index < users.size();index += batchsize)
            {
                for(int jndex=0;jndex < batchsize;jndex++)
                {
                    if(iterator.hasNext())
                    {
                        String user = iterator.next().toString();
                        String sql = "INSERT INTO user (userName) VALUE (" + user + ")";
                        statement.addBatch(sql);
                    }
                }
                //execute batch
                statement.executeBatch();
                //clear for the next batch
                statement.clearBatch();
            }

            end = System.currentTimeMillis();
            System.out.println("[end - begin]:" + (end - begin));
//
        } catch (SQLException e) {
            // Exception
            e.printStackTrace();
        } finally {
            try {

                // 5. close connection
                if(connection != null)
                    connection.close();
                if(statement != null)
                    statement.close();
                if(resultSet != null)
                    resultSet.close();

            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
